﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop.Excel;

namespace InsideDL.MISService
{    
    public class EXLAPP
    {
        bool findSheet = false;

        /// <summary>
        /// 目前開了多少 EXCEL
        /// </summary>
        public int XLSCount
        {
            get
            {
                return exlAPP == null ? 0 : exlAPP.Workbooks.Count;
            }
        }

        public Application exlAPP = null;
        Worksheet wsht = null;

        public EXLAPP()
        {
            exlAPP = new Application();
            exlAPP.DisplayAlerts = false;
        }

        public void SaveCloseXLS()
        {
            exlAPP.AlertBeforeOverwriting = false;
            for (int i = 1; i <= exlAPP.Workbooks.Count; i++)
            {
                exlAPP.Workbooks[i].Save();
            }
            exlAPP.Quit();
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(exlAPP);
                exlAPP = null;
            }
            catch (Exception ex)
            {

            }
            finally
            {
                GC.Collect();
            }
        }

        public void SetRangeValue(string p_shtName, int p_row, int p_col, string p_rngValue)
        {
            Range rng = getRange(p_shtName, p_row, p_col);
            rng.Value2 = p_rngValue;
        }

        public void SetRangeValue(string p_shtName,string p_rng, string p_rngValue)
        {
            Range rng = getRange(p_shtName, p_rng);
            rng.Value2 = p_rngValue;
        }


        /// <summary>
        /// 確認要找的Sheet 是否有存在
        /// </summary>
        /// <param name="p_shtName"></param>
        /// <returns></returns>
        public bool SheetExist(string p_shtName)
        {
            //Worksheet ws = getSheetObject(p_shtName);
            //return ws.Name.Equals(p_shtName);        
            getSheetObject(p_shtName);
            return findSheet;
        }

        public void CloseFile()
        {
            exlAPP.AlertBeforeOverwriting = false;
            object saveChange = false;
            for (int i = 1; i <= exlAPP.Workbooks.Count; i++)
            {
                exlAPP.Workbooks[i].Close(saveChange, Type.Missing
                    , Type.Missing);
            }
        }

        public void OpenFile(string p_fleName)
        {
            object oMissing = System.Reflection.Missing.Value;
            object updatelink = false;
            exlAPP.Workbooks.Open(p_fleName, updatelink, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing
                , oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
        }

        public void RefreshPivotTables(Worksheet p_wsht)
        {
            PivotTables pivotTables1 = p_wsht.PivotTables(Type.Missing) as PivotTables;
            if (pivotTables1.Count > 0)
            {
                for (int i = 1; i <= pivotTables1.Count; i++)
                {
                    pivotTables1.Item(i).RefreshTable();
                }
            }
        }

        public void RefreshPivotTables(string p_shtName)
        {
            RefreshPivotTables(getSheetObject(p_shtName));
        }


        /// <summary>
        /// 把資料清除再填入
        /// </summary>
        /// <param name="p_data">已取回的RecordSet</param>
        /// <param name="p_clearArea">要清除的區域</param>
        /// <param name="p_fillArea">要填入的區域</param>
        /// <param name="p_shtName">要工作的Sheet</param>
        public void CopyFromRecordset(ADODB.RecordsetClass p_data
            , string p_clearArea, string p_fillArea,string p_shtName)
        {
            wsht = getSheetObject(p_shtName);
            Range rng = wsht.get_Range(p_clearArea, Type.Missing);
            rng.ClearContents();
            rng = wsht.get_Range(p_fillArea, Type.Missing);
            rng.NumberFormatLocal = "@";
            int iRow = 2;
            rng = wsht.Cells[iRow, 1] as Range;
            rng.CopyFromRecordset(p_data, Type.Missing, Type.Missing);
        }


        /// <summary>
        ///
        /// </summary>
        /// <param name="p_sourceSht"></param>
        /// <param name="p_sourcrRange"></param>
        /// <param name="p_targetSht"></param>
        /// <param name="p_targetRange"></param>
        public void CopyPaste(string p_sourceSht,string p_sourceRange
            ,string p_targetSht,string p_targetRange)
        {
            CopyPaste(p_sourceSht, p_sourceRange, p_targetSht, p_targetRange, true);
        }

        public void CopyPaste(string p_sourceSht, string p_sourceRange
            , string p_targetSht, string p_targetRange,bool p_close)
        {
            //2 是因為第二個開的檔，所以指定到2
            Worksheet source = getSheetObject(p_sourceSht, 2);
            Worksheet target = getSheetObject(p_targetSht, 1);
            Range sourceRng = source.get_Range(p_sourceRange, Type.Missing);
            Range targetRng = target.get_Range(p_targetRange, Type.Missing);
            sourceRng.Copy(Type.Missing);
            targetRng.PasteSpecial(
                        XlPasteType.xlPasteValues
                        , XlPasteSpecialOperation.xlPasteSpecialOperationNone
                        , false, false);
            object saveChange = false;
            //用完就關掉，這樣，下一個再打開就會是第二個
            if (p_close.Equals(true))
            {
                exlAPP.Workbooks[2].Close(saveChange, Type.Missing, Type.Missing);
            }            
        }


        /// <summary>
        /// 取得指定的Sheet 中的指定Range 中的值
        /// </summary>
        /// <param name="p_shtName">指定Sheet</param>
        /// <param name="p_range">指定Range EX:B1</param>
        /// <returns></returns>
        public string GetSheetRangeValue(string p_shtName, string p_range)
        {
            Range rng = getRange(p_shtName, p_range);
            return rng.Value2 == null ? string.Empty : rng.Value2.ToString();
        }

        /// <summary>
        /// 取得指定的Sheet 中的指定Range 中的值改成在陣列中
        /// </summary>
        /// <param name="p_shtName">指定Sheet</param>
        /// <param name="p_range">指定Range EX:B1</param>
        /// <returns></returns>
        public object[,] GetSheetRangeArray(string p_shtName, string p_range)
        {
            Range rng = getRange(p_shtName, p_range);
            return rng == null ? null : rng.Value2 as object[,];            
        }

        public string[] GetSheetRangeArray(string p_shtName, string p_range,bool p_nothing)
        {
            object[,] tmp = GetSheetRangeArray(p_shtName, p_range);
            //var q = from x in tmp.Cast<string>() where x != null select x;
            var q = from x in tmp.Cast<string>() select x;
            return q.ToArray<string>();
        }

        public List<string> GetSheetList()
        {
            List<string> shtList = new List<string>();
            Microsoft.Office.Interop.Excel.Worksheet ws = null;
            //Workbook wb = exlAPP.Workbooks[p_pos];
            for (int i = 1; i <= exlAPP.Workbooks.Count; i++)
            {
                Workbook wb = exlAPP.Workbooks[i];
                for (int x = 1; x <= wb.Sheets.Count; x++)
                {
                    ws = (Worksheet)wb.Sheets[x];
                    shtList.Add(ws.Name);
                }
            }            
            return shtList;
        }

        protected Range getRange(string p_shtName, string p_range)
        {
            Range rng = null;
            wsht = getSheetObject(p_shtName);
            if (findSheet)
            {
                rng = wsht.get_Range(p_range, Type.Missing);
            }
            //if (SheetExist(p_shtName).Equals(true))
            //{
            //    if (wsht == null)
            //    {
            //        wsht = getSheetObject(p_shtName);
            //    }
            //    if (!wsht.Name.Equals(p_shtName))
            //    {
            //        wsht = getSheetObject(p_shtName);
            //    }
            //    rng = wsht.get_Range(p_range, Type.Missing);
            //}
            return rng;
        }

        protected Range getRange(string p_shtName, int p_row, int p_col)
        {
            Range rng = null;
            wsht = getSheetObject(p_shtName);
            rng = wsht.Cells[p_row, p_col] as Range;
            return rng;
        }

        protected string parseRangeToString(System.Collections.IEnumerator p_enum)
        {
            StringBuilder sb = new StringBuilder();
            while (p_enum.MoveNext())
            {
                if (p_enum.Current != null)
                {
                    sb.AppendFormat(",{0}", p_enum.Current.ToString());
                }
            }
            return sb.ToString().Substring(1);
        }

        /// <summary>
        /// 適合同時開多個workbook使用
        /// </summary>
        /// <param name="p_shtName"></param>
        /// <param name="p_pos">指定要開那一個WorkBook</param>
        /// <returns></returns>
        protected Worksheet getSheetObject(string p_shtName, int p_pos)
        {
            Microsoft.Office.Interop.Excel.Worksheet ws = null;
            Workbook wb = exlAPP.Workbooks[p_pos];

            for (int i = 1; i <= wb.Sheets.Count; i++)
            {
                ws = (Worksheet)wb.Sheets[i];
                if (ws.Name.Trim().Equals(p_shtName))
                {
                    findSheet = true;
                    break;
                }
            }
            return ws;
        }

        /// <summary>
        /// 取回在EXCEL中所指定的Sheet
        /// </summary>
        /// <param name="p_shtName"></param>
        /// <returns></returns>
        protected Worksheet getSheetObject(string p_shtName)
        {
            Microsoft.Office.Interop.Excel.Worksheet ws = null;
            findSheet = false;
            for (int i = 1; i <= exlAPP.Sheets.Count; i++)
            {
                ws = getSheetObject(p_shtName, i);
                if (findSheet.Equals(true))
                { break; }
            }
            return ws;
        }

    }
}
